﻿using BySoft.Server.Common;
using BySoft.Server.Common.Entity;
using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess
{
    public partial class BsOutmatreturnDA
    {
        //主档，明细档，材料，供应商，工序
        public static DataTable SelectList(string strBillID, string strWorkOrderNo, string strSupplierID, string strFrom, string strEnd, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                string strcmd = @"select  a.*,boo.OutProcessID,mp.ProcessName
 ,d.Nums, d.OutOrderDetailID, d.ReturnReason, d.CompensateAmount, d.MaterialID
 
   ,s.SupplierName,
   m.MaterialName,m.Specifications,m.Unit,
   mt.MaterialTypeName, mmst.MaterialSmallTypeName,
  -1 as IsNew,-1 as IsDel
from Bs_OutmatReturn a
LEFT JOIN Bs_OutmatReturnDetail d ON d.BillID = a.BillID
LEFT JOIN BS_OutOrder AS boo ON a.OutOrderID=boo.BillID
LEFT JOIN md_supplier s  on boo.SupplierID =s.SupplierID
LEFT JOIN MD_Process AS mp ON boo.OutProcessID=mp.ProcessID

LEFT JOIN md_material m ON d.MaterialID=m.MaterialID
LEFT JOIN md_materialType mt ON m.MaterialTypeID=mt.MaterialTypeID
LEFT  JOIN MD_MaterialSmallType AS mmst ON mmst.MaterialSmallTypeID = m.MaterialSmallTypeID

  WHERE a.[Status]='1' ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and a.WorkOrderNo like @strWorkOrderNo";
                    dpc.AddWithValue("@strWorkOrderNo", "%" + strWorkOrderNo + "%");
                }

                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and a.BillID like @BillID";
                    dpc.AddWithValue("@BillID", "%" + strBillID + "%");
                }
                if (!string.IsNullOrEmpty(strSupplierID))
                {
                    strcmd += " and a.SupplierID = @SupplierID";
                    dpc.AddWithValue("@SupplierID",  strSupplierID);
                }
                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and a.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strEnd))
                {
                    strcmd += " and a.BillDate <= @strEnd";
                    dpc.AddWithValue("@strEnd", strEnd );
                }
                strcmd += " order by a.BillDate desc";
                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

      
  
       
    public static DataTable SelectSendCanbackNum(string strCurrentReturnBillID,string sendID, string materialID) {
     
        
         try
            {

                //未填过
                string strcmd = @"SELECT d.MaterialID,d.BillID,
       d.Nums                           as SendNum,
      
       d.nums -ISNULL(ret.returnNums, 0) AS CanBackNum --可退数量 
      
from Bs_OutOrder a
left join Bs_OutOrderDetail d on a.BillID=d.BillID
LEFT JOIN  (
        ---已退数量
          select OutOrderID,materialID,sum(Nums) as returnNums 
            from Bs_OutmatReturn aR 
             LEFT JOIN Bs_OutmatReturnDetail dR on aR.billId=dR.BillID 
            -- where aR.BillID<>@CurrentBillID
             group by  OutOrderID,materialID
             )  ret on ret.OutOrderID=a.BillID and ret.materialID=d.materialID
WHERE  d.nums -ISNULL(ret.returnNums, 0)>0
    

";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@CurrentBillID", strCurrentReturnBillID);//排除本单以外的数量
      
                if (!string.IsNullOrEmpty(sendID))
                {
                    strcmd += " and d.BillID = @BillID";
                    dpc.AddWithValue("@BillID", sendID);
                }
               
                if (!string.IsNullOrEmpty(materialID))
                {
                    strcmd += " and d.MaterialID = @MaterialID";
                    dpc.AddWithValue("@MaterialID", materialID);
                }

                strcmd += " order by BillID ";

                DataTable dt = DataAccessCommon.Select(strcmd,  dpc).Tables[0];
             

                return dt;
             }
            catch (Exception ex)
            {
                throw ex;
            }
        
        }

        /// <summary>
        /// 查询OutOrder可退回的Material明细
        /// </summary>
        /// <param name="strBillID">本单号码，新增时为空</param>
        /// <param name="strProcessID">工序</param>
        /// <returns></returns>
        public static DataTable SelectOutProcessDetail(string strCurrentReturnBillID,string strBillID,string strWorkOrderNo,string strSupplierID, string strProcessID
            ,int  pageIndex, int PageSize, out int m_count)
        {
            try
            {

                //未填过
                string strcmd = @"SELECT a.*,
       a.OutProcessID as ProcessID,
       mp.ProcessName                   AS OutProcessName,
       d.DetailID,
       d.materialID,
       d.Nums                           as SendNum,
       sply.SupplierName,
       m.MaterialName,
       m.Specifications,
       m.Unit,
       mt.MaterialTypeName,
       mmst.MaterialSmallTypeName,
       d.nums -ISNULL(ret.returnNums, 0) AS CanBackNum,	--可退数量 
       d.nums -ISNULL(ret.returnNums, 0) AS Nums,	--可退数量 
       d.DetailID                       AS OutOrderDetailID,
       SPACE(200)                       AS ReturnReason,
       0.00                             AS CompensateAmount,
       -1                               AS IsNew,
       -1                               AS IsDel
from Bs_OutOrder a
left join Bs_OutOrderDetail d on a.BillID=d.BillID
left join md_process mp on a.OutProcessID=mp.ProcessID
LEFT JOIN md_material m ON d.MaterialID=m.MaterialID
LEFT JOIN md_materialType mt ON m.MaterialTypeID=mt.MaterialTypeID
LEFT  JOIN MD_MaterialSmallType AS mmst ON mmst.MaterialSmallTypeID = m.MaterialSmallTypeID
Left join md_supplier sply  on a.supplierID=sply.supplierID
LEFT JOIN  (
        ---已退数量
          select OutOrderID,materialID,sum(Nums) as returnNums 
            from Bs_OutmatReturn aR 
             LEFT JOIN Bs_OutmatReturnDetail dR on aR.billId=dR.BillID 
             where aR.BillID<>@CurrentBillID
             group by  OutOrderID,materialID
             )  ret on ret.OutOrderID=a.BillID and ret.materialID=d.materialID
WHERE  d.nums -ISNULL(ret.returnNums, 0)>0
    

";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@CurrentBillID", strCurrentReturnBillID);//排除本单以外的数量
      
                if (!string.IsNullOrEmpty(strBillID))
                {
                    strcmd += " and a.BillID = @BillID";
                    dpc.AddWithValue("@BillID", strBillID);
                }
                if (!string.IsNullOrEmpty(strSupplierID))
                {
                    strcmd += " and a.SupplierID = @strSupplierID";
                    dpc.AddWithValue("@strSupplierID", strSupplierID);
                }
                if (!string.IsNullOrEmpty(strProcessID))
                {
                    strcmd += " and a.ProcessID = @strProcessID";
                    dpc.AddWithValue("@strProcessID", strProcessID);
                }

                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and a.WorkOrderNo like  @strWorkOrderNo";
                    dpc.AddWithValue("@strWorkOrderNo", "%"+strWorkOrderNo+"%");
                }
                strcmd += " order by BillID ";

                DataTable dt = DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                //dt.Columns.Add("ReturnReason", typeof(string));
                //dt.Columns.Add("CompensateAmount", typeof(decimal));

                return dt;
             }
            catch (Exception ex)
            {
                throw ex;
            }
        }

     
        #region View Data
        /// <summary>
        /// Header
        /// </summary>
        /// <param name="strBillID"></param>
        /// <returns></returns>
        public static DataTable SelectHeadData(string strBillID)
        {
            try
            {
                string strcmd = @"select  a.*,boo.OutProcessID,mp.ProcessName
   , boo.SupplierID,s.SupplierName,boo.BillID,boo.BillDate,boo.ScheduleID,
  -1 as IsNew,-1 as IsDel
from Bs_Outmatreturn a
LEFT JOIN BS_OutOrder AS boo ON a.OutOrderID=boo.BillID
LEFT JOIN md_supplier s  on boo.SupplierID =s.SupplierID
LEFT JOIN MD_Process AS mp ON boo.OutProcessID=mp.ProcessID  
where a.BillID=@BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@BillID", strBillID);
                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// Detail
        /// </summary>
        /// <param name="strBillID"></param>
        /// <returns></returns>
        public static DataTable SelectDetailData(string strBillID)
        {
            try
            {
                string strcmd = @"select a.*,m.MaterialName,m.Specifications,m.Unit,
                 mt.MaterialTypeName,
                 mmst.MaterialSmallTypeName,
                od.Nums  as SendNum,
                od.nums  AS CanBackNum,	--可退数量 
     
                -1 as IsNew,-1 as IsDel
 from Bs_OutmatreturnDetail a
LEFT JOIN Bs_outOrderDetail od ON a.OutOrderDetailID=od.DetailID 
LEFT JOIN md_material m ON od.MaterialID=m.MaterialID
LEFT JOIN md_materialType mt ON m.MaterialTypeID=mt.MaterialTypeID
LEFT JOIN MD_MaterialSmallType AS mmst ON mmst.MaterialSmallTypeID = m.MaterialSmallTypeID

where a.BillID=@BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@BillID", strBillID);
                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

        } 
        #endregion



        #region AUD
        public static bool SaveOrUpdate(BsOutmatreturnEntity entity, DataTable OutmatreturnDetailDT, bool isNew)
        {
            bool istag = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                broker.BeginTransaction();
                try
                {
                    if (isNew)
                    {
                        DataAccessCommon.Insert(entity, broker);
                    }
                    else
                    {
                        DataAccessCommon.UpdateOnly(entity, broker);
                        ClsCommDA.Del("BS_OutmatreturnDetail", "BillID",entity.BillID, broker);//delete  first  & then insert new 
                     

                    }
                    if (OutmatreturnDetailDT != null && OutmatreturnDetailDT.Rows.Count > 0)
                    {
                        foreach (DataRow drItem in OutmatreturnDetailDT.Rows)
                        {
                            BsOutmatreturndetailEntity DetailEntity = new BsOutmatreturndetailEntity();
                            DetailEntity.BillID = entity.BillID;
                            DetailEntity.Nums = CommonMethod.ConvertToDecimal(drItem["Nums"]);
                            DetailEntity.OutOrderDetailID = CommonMethod.ConvertToDecimal(drItem["OutOrderDetailID"],2);
                            
                            DetailEntity.ReturnReason = CommonMethod.GetString(drItem["ReturnReason"]);
                            DetailEntity.CompensateAmount = CommonMethod.ConvertToDecimal(drItem["CompensateAmount"],2);
                            DetailEntity.MaterialID = CommonMethod.GetString(drItem["MaterialID"]);
                     

                            string strIsNew = CommonMethod.GetString(drItem["IsNew"]);
                            string strIsDel = CommonMethod.GetString(drItem["IsDel"]);
                            if (strIsNew == "1")//insert
                            {
                                DataAccessCommon.Insert(DetailEntity, true, broker);
                            }
                            else
                            {
                                if ( strIsDel != "1")//update
                                {
                                    //DetailEntity.DetailID = CommonMethod.ConvertToDecimal(drItem["DetailID"]);
                                    //DataAccessCommon.UpdateOnly(DetailEntity, broker);
                                    DataAccessCommon.Insert(DetailEntity, true, broker);
                                
                                }
                                else if ( strIsDel == "1")
                                {
                                    //删除明细  不会用到这里，@See Del()
                                    ClsCommDA.Del("BS_OutmatreturnDetail", "DetailID", CommonMethod.GetString(drItem["DetailID"]), broker);
                                }
                               

                            }
                           
                        }
                    }
                    broker.Commit();
                }
                catch
                {
                    istag = false;
                    broker.Rollback();
                    throw;
                }
                return istag;
            }
        }

        public static bool Del(string strCode)
        {
            bool isResult = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    DataAccessCommon.Delete(strCode, "BillID", "BS_OutmatreturnDetail");
                    DataAccessCommon.Delete(strCode, "BillID", "BS_Outmatreturn");
                    broker.Commit();
                }
                catch (Exception ex)
                {
                    isResult = false;
                    broker.Rollback();
                    throw ex;
                }
            }
            return isResult;
        } 
        #endregion
    }
}
